<CFSET dDate = DATEFORMAT(Now(),"mm/dd/yyyy")> 
<CFMODULE TEMPLATE="#client.common_path#cfm_DBdateFormat.cfm" dateConvert="#dDate#">
<CFSET sDate1 = dateResult>

<CFSET sMessPostApproval="Uploading of force leave was successful.">
<CFQUERY NAME="qryCINFORCELV" DATASOURCE="#client.company_dsn#">
	SELECT *
	FROM ECINFORCELV
	WHERE ROUTEID = '#GetRecord.ROUTEID#'
</CFQUERY>

<CFIF qryCINFORCELV.RecordCount>
	<CFTRANSACTION>
	<CFTRY>
		<CFSET sNotPosted="">
		<!--- Maximum of 100 employees subject for force leave --->
		<CFLOOP INDEX="x" FROM="1" TO="100">
			<CFIF IsDefined("qryCINFORCELV.PERSONNELIDNO#x#") AND Evaluate("qryCINFORCELV.PERSONNELIDNO" & x) NEQ "">
				<CFSET sPIN=Evaluate("qryCINFORCELV.PERSONNELIDNO" & x)>

				<!--- Check workingdayscode --->
				<CFQUERY NAME="qryCMFPA" DATASOURCE="#client.company_dsn#">
					SELECT WORKINGDAYSCODE, FIRSTNAME, MIDDLENAME, LASTNAME
					FROM CMFPA
					WHERE PERSONNELIDNO = '#sPIN#'
				</CFQUERY>

				<CFIF qryCMFPA.RecordCount>
					<CFQUERY NAME="qryCLKWORKINGDAYS" DATASOURCE="#client.company_dsn#">
						SELECT WORKINGHOURSPDAY
						FROM CLKWORKINGDAYS
						WHERE WORKINGDAYSCODE = '#qryCMFPA.WORKINGDAYSCODE#'
					</CFQUERY>

					<CFIF qryCLKWORKINGDAYS.RecordCount>
						<CFMODULE TEMPLATE="#client.common_path#cfm_DBdateFormat.cfm" dateConvert="#qryCINFORCELV.STARTINGDATE#">
						<CFSET sDate2 = dateResult>
						<CFMODULE TEMPLATE="#client.common_path#cfm_DBdateFormat.cfm" dateConvert="#qryCINFORCELV.ENDINGDATE#">
						<CFSET sDate3 = dateResult>
					
						<CFQUERY NAME="qryCINLEAVEAPPSM" DATASOURCE=#client.company_dsn#>
							INSERT INTO CINLEAVEAPPSM
								(PERSONNELIDNO,
								 STARTINGDATE,
								 LEAVETYPE,
								 APPROVED,
								 APPLICATIONDATE,
								 DOCNUMBER,
								 ENDINGDATE,
								 LEAVEHOURS,
								 WITHPAY,
								 REMARKS)
							VALUES
								('#sPIN#',
								 #preserveSingleQuotes(sDate2)#,
								 '#qryCINFORCELV.LEAVETYPE#',
								 'Y',
								 #preserveSingleQuotes(sDate1)#,
								 '#qryCINFORCELV.DOCNUMBER#',
								 #preserveSingleQuotes(sDate3)#,
								 #DateDiff("d", CreateODBCDate(qryCINFORCELV.STARTINGDATE), CreateODBCDate(qryCINFORCELV.ENDINGDATE)) * qryCLKWORKINGDAYS.WORKINGHOURSPDAY#,
								 'Y',
								 'Force Leave Application from iBOS/e')
						</CFQUERY>
					<CFELSE>
						<CFSET sNotPosted=sNotPosted & "&nbsp;&nbsp;&nbsp;" & sPin & " (#qryCMFPA.FIRSTNAME# #qryCMFPA.MIDDLENAME# #qryCMFPA.LASTNAME#) -- #qryCMFPA.WORKINGDAYSCODE# is not found in lookup table.<BR>">
					</CFIF>
				<CFELSE>
					<CFSET sNotPosted=sNotPosted & "&nbsp;&nbsp;&nbsp;" & sPin & " (#qryCMFPA.FIRSTNAME# #qryCMFPA.MIDDLENAME# #qryCMFPA.LASTNAME#) -- Not found in Active Employee File.<BR>">
				</CFIF>
			</CFIF>
		</CFLOOP>

		<CFTRANSACTION ACTION="COMMIT"/>
		<CFCATCH TYPE="Database">
			<CFTRANSACTION ACTION="ROLLBACK"/>
			<CFSET sNotPosted="">
			<CFSET sMessPostApproval="Error in post-approval activity ...<br><br>#CFCATCH.message# #CFCATCH.DETAIL#">
		</CFCATCH>

		<CFIF sNotPosted NEQ "">
			<CFSET sMessPostApproval=sMessPostApproval & " Except for the following employees ...<BR>" & sNotPosted>
		</CFIF>
	</CFTRY>
	</CFTRANSACTION>
<CFELSE>
	<CFSET sMessPostApproval="WARNING!!! Leave Balance has not been updated. Force Leave Application is missing.<BR>Please contact your administrator.">
</CFIF>